import os

import pandas as pd
'''excel 文件合并测试'''
if __name__ == "__main__":
    # 存放index相同的sheet的数据
    frames = {}
    for i in range(100):
        frames[i] = []
    info = ""
    max_sheet_index = 1
    writer = pd.ExcelWriter(r"C:\Users\long\Desktop\2020年出入库数据\1.xlsx")
    try:
        for dirpath, dirnames, filenames in os.walk(r"C:\Users\long\Desktop\2020年出入库数据"):
            for filename in filenames:
                inputfilename = ""
                try:
                    if filename == "1.xlsx" or not filename.__contains__(".xlsx"):
                        continue
                    inputfilename = dirpath.replace("\\", "/") + '/' + filename
                    file = pd.ExcelFile(inputfilename)
                    print(file.sheet_names)
                    max_sheet_index_temp = file.sheet_names.__len__()
                    if max_sheet_index_temp > max_sheet_index:
                        max_sheet_index = max_sheet_index_temp
                    for i in range(max_sheet_index_temp):
                        print(file.sheet_names[i])
                        df = pd.read_excel(inputfilename, sheet_name=i)  # excel转换成DataFrame
                        frames[i].append(df)
                    print(
                        "concatfile success:" + inputfilename + " to 1.xlsx")
                    info += "concatfile success:" + inputfilename + " to 1.xlsx"
                except FileExistsError as f:
                    print(f)
                    continue
        for i in range(max_sheet_index):
            result = pd.concat(frames[i])
            # result1 = result.loc[result["业务发生时间"].notna()]
            result.to_excel(writer, sheet_name="sheet" + str(i), index=0)
        writer.save()
    except RuntimeError as af:
        print(af)
